﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using UTSDAL.SQL;
using System.Data.SqlClient;
using System.Data;
using UTSDAL.LINQSQL.Model;
using UTSDAL.LINQSQL.DAO;
using UTSDAL.LINQSQL.Helper;

namespace UTSConsole
{
    class SQLConsoleProgram
    {


        public static void Main(String[] args)
        {
            DateTime dateValue = new DateTime(2009, 3, 21);

            Console.WriteLine((int)dateValue.DayOfWeek);      // Displays 3



            //FillDayWithEvent("Monday","Room 0.2");
            //TestTimetableEventGeneration();
        }

        public static void TestTimetableEventGeneration()
        {


            Semester semester = new SemesterDAO().findById(new Guid("00000000-0000-0000-0000-000000000000"));

            var query = from cm in semester.CohortModules
                        where cm.ModuleId.Equals(new Guid("7B6691F4-F081-4163-A518-329E7CDB176F"))
                        select cm;

            CohortModule cohortModule = query.SingleOrDefault<CohortModule>();

            List<ModuleEvent> moduleEvents = new ModuleEventDAO().getUnAllocatedModuleEventBySemesterAndModule(semester, cohortModule.Module);
            Console.WriteLine("Generating");
            TimetableEvent timetableEvent = new TimetableEventDAO().generate(semester, cohortModule.Cohort, moduleEvents.ElementAt(0));

            Console.WriteLine("Module Event = " + timetableEvent.ModuleEventId);
            Console.WriteLine("Generated Day = " + timetableEvent.CourseDay.CourseDayName);
            Console.WriteLine("Generated Start Time = " + timetableEvent.StartTime);
            Console.WriteLine("Generated End Time = " + timetableEvent.EndTime);
            Console.WriteLine("Generated Room = " + timetableEvent.Room.RoomName);
            
        }


        public static void Main22(String[] args)
        {
            UTSDataContext context = DataContextHelper.Context;

            ModuleEvent moduleEvent = (from e in context.ModuleEvents
                                       where e.ModuleId.Equals(new Guid("7B6691F4-F081-4163-A518-329E7CDB176F"))
                                       select e).SingleOrDefault<ModuleEvent>();



            foreach (CourseDay day in context.CourseDays)
            {
                List<TimetableEvent> courseDayEvents = 
                                               (from e in context.TimetableEvents
                                               where e.CourseDay.Equals(day)
                                               select e).ToList<TimetableEvent>();
                
                Console.WriteLine("Course Day Events found for " + day.CourseDayName + "   = " + courseDayEvents.Count);

                foreach (TimetableEvent timetableEvent in courseDayEvents)
                {
                    //timetableEvent.
                }

            }



        }



        public static void FillDayWithEvent(String courseDayName,String roomName)
        {
            UTSDataContext context = new UTSDataContext();

            TimetableEvent timetableEventToUpdate = new TimetableEvent();
            timetableEventToUpdate.TimetableEventId = Guid.NewGuid();


            var moduleVar = from m in context.Modules
                            where m.ModuleName.Equals("Distributed System")
                            select m;

            UTSDAL.LINQSQL.Model.Module module = moduleVar.SingleOrDefault<UTSDAL.LINQSQL.Model.Module>();
            timetableEventToUpdate.ModuleId = module.ModuleId;
            timetableEventToUpdate.ModuleEventId = module.ModuleEvents.ElementAt(0).ModuleEventId;


            SettingsDAO settingsDAO = new SettingsDAO();
            timetableEventToUpdate.StartTime = settingsDAO.getStartTime();
            timetableEventToUpdate.EndTime = settingsDAO.getEndTime();

            timetableEventToUpdate.CourseDayId = new CourseDayDAO().findByCourseDayName(courseDayName).CourseDayId;
            timetableEventToUpdate.RoomId = new RoomDAO().findRoomByRoomName(roomName).RoomId;
            timetableEventToUpdate.TimetableEventTypeId = new TimetableEventTypeDAO().getReservationEventType().TimetableEventTypeId;

            new TimetableEventDAO().insert(timetableEventToUpdate);


        }


        public static void addTimetableEvent(String[] args)
        {
            UTSDataContext context = new UTSDataContext();

            TimetableEvent timetableEventToUpdate = new TimetableEvent();
            timetableEventToUpdate.TimetableEventId = Guid.NewGuid();


            var moduleVar = from m in context.Modules
                            where m.ModuleName.Equals("Distributed System")
                            select m;

            UTSDAL.LINQSQL.Model.Module module = moduleVar.SingleOrDefault<UTSDAL.LINQSQL.Model.Module>();
            timetableEventToUpdate.ModuleId = module.ModuleId;
            timetableEventToUpdate.ModuleEventId = module.ModuleEvents.ElementAt(0).ModuleEventId;


            timetableEventToUpdate.StartTime = TimeSpan.Parse("10:30");
            timetableEventToUpdate.EndTime = TimeSpan.Parse("12:30");

            timetableEventToUpdate.CourseDayId = context.CourseDays.ToList<CourseDay>().ElementAt(0).CourseDayId;
            timetableEventToUpdate.RoomId = context.Rooms.ToList<Room>().ElementAt(0).RoomId;
            timetableEventToUpdate.TimetableEventTypeId = new TimetableEventTypeDAO().getReservationEventType().TimetableEventTypeId;

            new TimetableEventDAO().insert(timetableEventToUpdate);


        }


        public static void genericsql(String[] args)
        {
            ConnectionManager connectionManager = ConnectionManager.getInstance();

            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO Building VALUES(@param1,@param2,@param3)");

            List<SqlParameter> parameters = new List<SqlParameter>();

            SqlParameter buildingId = new SqlParameter("@param1", SqlDbType.UniqueIdentifier);
            buildingId.Value = Guid.NewGuid();
            parameters.Add(buildingId);

            SqlParameter buildingName = new SqlParameter("@param2", SqlDbType.VarChar);
            buildingName.Value = "SQL Added Building";
            parameters.Add(buildingName);


            SqlParameter departmentId = new SqlParameter("@param3", SqlDbType.UniqueIdentifier);
            UTSDataContext context = new UTSDataContext();

            departmentId.Value = context.Departments.SingleOrDefault<Department>().DepartmentId;
            parameters.Add(departmentId);

            connectionManager.update(query.ToString(), parameters);
        }



        public static void update(String query, List<SqlParameter> parameters)
        {
            SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["UTSDAL.Properties.Settings.UTSDBConnectionString"].ConnectionString);
            connection.Open();

            SqlCommand command = new SqlCommand(query, connection);

            foreach (SqlParameter p in parameters)
            {
                command.Parameters.Add(p);
            }

            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}
